<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="refresh" content="60" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Dispatch Report Data</title>
<link href="UniversalStyleSheet.css" rel="stylesheet" type="text/css" />
<script language="javascript">
function OpenTicket(ticket,contract)
{
user=parent.parent.parent.HeaderBarFrame.frames(0).username.value
url="accountticketframeset.asp?FromDispatch=yes&ticketid="+ticket+"&ContractID="+contract+"&user="+user
title="ticket_"+ticket

ticketwindow=window.open(url,title,"height=700,width=525")
}

var timer=60
window.setTimeout("fCountDown();",1000)

function fCountDown()
{
cd=document.getElementById("countdown")
timer=timer-1
cd.innerHTML=timer + " seconds"
window.setTimeout("fCountDown();",1000)
}

function sortby(what)
{
	zipval="<%= request.QueryString("zip") %>"
	fromval="<%= request.QueryString("from") %>"
	toval="<%= request.QueryString("to") %>"
	sortval=what
	city="<%= request.QueryString("city") %>"
	window.location.href="dispatchreportdata.asp?zip="+zipval+"&from="+fromval+"&to="+toval+"&sort="+sortval + "&city=" + city
}
</script>
</head>

<body>

<%
dim db,rs,custDB,custRS,nonDB,nonRS,colorDB,colorRS,AHdb,AHrs
set db=server.CreateObject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")
set custDB=server.CreateObject("adodb.connection")
set custRS=server.CreateObject("adodb.recordset")
set nonRS=server.CreateObject("adodb.recordset")
set colorDB=server.CreateObject("adodb.connection")
set colorRS=server.CreateObject("adodb.recordset")

FilePath=server.MapPath("/afterhours/access/backend/tickets.mdb")
FilePath2=server.MapPath("/afterhours/access/backend/homesmart_customers.mdb")

db.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath)
custDB.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath2)
zip=request.QueryString("zip")
from=request.QueryString("from")
city=request.QueryString("city")

if city<>"" then 'get city list
	set AHdb=server.CreateObject("adodb.connection")
	set AHrs=server.CreateObject("adodb.recordset")
	AHfilepath=server.MapPath("/afterhours/access/backend/afterhours_be.mdb")
	AHdb.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AHfilepath)
	AHsql="select zip from COzips where city='" & city & "'"
	AHrs.open AHsql,AHdb,1,2
	
	if AHrs.eof then
		citylist="0"
	else
		AHra=AHrs.getrows()
		for x=0 to ubound(AHra,2)
			citylist=citylist & AHra(0,x) & ","
		next
		citylist=left(citylist,len(citylist)-1)
		
	end if
	
	AHrs.close
	AHdb.close
	set AHrs=nothing
	set AHdb=nothing

end if



if from="" then 
	from=date()
else
	from=cdate(from)
end if

qTo=request.QueryString("to")
if qTo="" then 
	qTo=dateadd("d",1,date())
else
	qTo=dateadd("d",1,cdate(qTo))
end if

sortcol=request.QueryString("sort")
if sortcol="" then sortcol="created_time"

response.Write("<span class='ResultsAddress'>Filter: Date Range(" & from & " - " & dateadd("d",-1,cdate(qTo)) & ") - Zip(" & zip & ") - City(" & city & ") - Sorted By(" & sortcol & ")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Auto-Refresh in:&nbsp;</span>")
response.Write("<span id='countdown' name='countdown' class='ResultsAddress'>60 seconds</span><br/>")

sql="select ticket_id, contract_id, status, created_time, completed_time, appliance, emergency, follow_up from tickets where (created_time between #" & from & "# and #" & qTo & "#) order by " & sortcol
rs.open sql,db,1,2
if not rs.eof then
	 ra=rs.getrows() '0 ticket_ID, 1 contract_id, 2 status, 3 created_time, 4 completed_time, 5 appliance, 6 emergency, 7 follow up
	 rs.close
	 x=0
	 do until x>ubound(ra,2)'this loop adds the found contract_ids to a list for the in clause
	 	contractList=contractList & "'" & ra(1,x) & "',"
		x=x+1
	loop
	contractList=left(contractList,len(contractList)-1)
	
	custSQL="select zip,address,contract_id from customers where contract_id in (" & contractList & ")"
	
	custRS.open custSQL,custDB,1,2
	if not custRS.eof then
		custRA=custRS.getrows()
		custRS.close
	else
		cust="none"
	end if
		
	nonSQL="select zip,address,contract_id from non_homesmart_customers where contract_id in (" & contractList & ")"
	nonRS.open nonSQL,db,1,2
	if not nonRS.eof then
		nonRA=nonRS.getrows()
		nonRS.close
	else
		non="none"
	end if
	
	db.close
	custdb.close
	if cust<>"none" then rows=ubound(custRA,2)
	if non<>"none" then rows=rows+ubound(nonRA,2)+1
	redim acctRA(2,rows)
	
	z=0'create array of cust and non-cust combined for use in mainRA
	if cust<>"none" then
		for x=0 to ubound(custRA,2)
			acctRA(0,z)=custRA(0,x)
			acctRA(1,z)=custRA(1,x)
			acctRA(2,z)=custRA(2,x)
			z=z+1
		next
	end if
	
	if non<>"none" then
		for x=0 to ubound(nonRA,2)
			acctRA(0,z)=nonRA(0,x)
			acctRA(1,z)=nonRA(1,x)
			acctRA(2,z)=nonRA(2,x)
			z=z+1
		next
	end if
	
	redim TmainRA(9,ubound(ra,2))'0 ticket_ID, 1 contract_id, 2 status, 3 created_time, 4 completed_time, 5 appliance, 6 emergency, 7 zip, 8 address, 9 followup
	z=0
	for x=0 to ubound(ra,2)'build TmainRA of ticket info and account info
		for y=0 to ubound(ra,1)
			TmainRA(y,x)=ra(y,x)
		next
		for zz=0 to ubound(acctRA,2)
			if acctRA(2,zz)=TmainRA(1,x) then'checking to see if acctRA contract_id  = mainRA cont_ID
				TmainRA(8,x)=acctRA(0,zz)
				TmainRA(9,x)=acctRA(1,zz)
				exit for
			end if
		next
	next
	
	'filters 
	redim mainRA(9,ubound(TmainRA,2))'now filter for zipcode if zipcode given
	if zip<>"" then
		zip=cstr(zip)
		yy=0
		for y=0 to ubound(TmainRA,2)
			comparezip=cstr(TmainRA(8,y))
			if comparezip=zip then
				for x=0 to ubound(TmainRA,1)
					mainRA(x,yy)=TmainRA(x,y)
				next 
				yy=yy+1
			end if
		next
		yy=yy-1
		if yy=-1 then 
			yy=0
			results="none"
		end if
		redim preserve mainRA(9,yy)'size it down to actual size
				
	elseif city<>"" then 'check if filtering for a city
		city=cstr(city)
		yy=0
		for y=0 to ubound(TmainRA,2)
			comparezip=cstr(TmainRA(8,y))
			if instr(citylist,comparezip)>0 then
				for x=0 to ubound(TmainRA,1)
					mainRA(x,yy)=TmainRA(x,y)
				next
				yy=yy+1
			end if
		next
		yy=yy-1
		if yy=-1 then
			yy=0
			results="none"
		end if
		redim preserve mainRA(9,yy)	
	else 'zip="" so copy TmainRA to mainRA
		
		for y=0 to ubound(TmainRA,2)
			for x=0 to ubound(TmainRA,1)
				mainRA(x,y)=TmainRA(x,y)
			next
		next
	end if
	
	'create a string that will be the html table output
	if results<>"none" then
		filepathcolor=server.MapPath("/afterhours/access/backend/admin.mdb")
		colorDB.open ("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & filepathcolor)
		colorRS.open "dispatchcolors",colorDB,1,2
		cOpen=colorRS("open")
		cClosed=colorRS("closed")
		cCompleted=colorRS("completed")
		cCancelled=colorRS("cancelled")
		cDispatched=colorRS("dispatched")
		cDispatchedSpecial=colorRS("dispatchedSpecial")
		cFollowUP=colorRS("followup")
		colorRS.close
		colorDB.close
	%>
		<table width='910' border='0' bgcolor='#009900' cellspacing='1' cellpadding='0'>
		 <tr class='DispatchHeaders' bgcolor='#FFFFFF'>
        <td width='53' height='20' ><a href="javascript: sortby('ticket_id')">Ticket#</a></td>
        <td width='52' height='20' ><a href="javascript: sortby('contract_id')">Cont ID</a></td>
        <td width='46' height='20' >Zip</td>
        <td width='150' height='20'>Address </td>
        <td width='103' height='20'><a href="javascript: sortby('appliance')">Appliance </a></td>
        <td width='48' height='20'><a href="javascript: sortby('emergency')">Emerg.</a> </td>
        <td width='125' height='20'><a href="javascript: sortby('created_time')">Created</a> </td>
        <td width='125' height='20'><a href="javascript: sortby('completed_time')">Completed </a></td>
        <td width='72' height='20'><a href="javascript: sortby('status')"> Status</a></td>
      	<td width='125' height='20'><a href="javascript: sortby('follow_up')">Follow-Up</a></td>
	  </tr>
	<%
		for x=0 to ubound(mainRA,2)
			select case mainRA(2,x)
				case "Open"
					color=cOpen
				case "Closed"
					color=cClosed
				case "Completed"
					color=cCompleted
				case "Cancelled"
					color=cCancelled
				case "Dispatched"
					color=cDispatched
				case "Follow Up"
					color=cFollowup
				case else
					color="ffffff"
			end select
			if (mainRA(2,x)="Open" or mainRA(2,x)="Dispatched") and (cdate(mainRA(3,x))<dateadd("h",-1,now())) then color=cDispatchedSpecial
			%>	
			<tr bgcolor="#<%= color %>" onmouseover="this.bgColor='#ffff99'" onmouseout="this.bgColor='#<%= color %>'" onclick="javascript: OpenTicket('<%= mainRA(0,x) %>','<%= mainRA(1,x) %>')" class="DispatchData" height="20">
			<td><%= mainRA(0,x) %></td>
			<td><%= mainRA(1,x) %></td>
			<td><%= mainRA(8,x) %></td>
			<td><%= mainRA(9,x) %></td>
			<td><%= mainRA(5,x) %></td>
			<td><%= mainRA(6,x) %></td>
			<td><%= mainRA(3,x) %></td>
			<td><%= mainRA(4,x) %></td>
			<td><%= mainRA(2,x) %></td>
			<td><%= mainRA(7,x) %></td>
			</tr>
		<%
		next
		%>
</table>
	<%
	else
		%>
		<span class='TableTitles'>(None)</span>
		<%
	end if
else
	%>
	<span class='TableTitles'>(None)</span>
	<%
end if 'for if rs.eof

set custDB=nothing
set custRS=nothing
set rs=nothing
set db=nothing
set colorDB=nothing
set colorRS=nothing
%>
</body>
</html>
